namespace RSSReader.DAL.Migrations
{
  using System;
  using System.Data.Entity.Migrations;

  public partial class UpdatingViews : DbMigration
  {
    public override void Up()
    {
      ApplicationDbContext context = new ApplicationDbContext();
      context.Database.ExecuteSqlCommand("DROP VIEW dbo.UserSubscriptionVW");
      context.Database.ExecuteSqlCommand("DROP VIEW dbo.UserArticleVW");

      this.CreateView("dbo.UserSubscriptionVW",
          @"select  SRC.Id as SourceId,
	                  SRC.Url as Url,
	                  SUB.UserId as UserId,
	                  SUB.IsSubscribe as IsSubscribe,
					          SRC.Description as Description,
					          ISNULL(SUB.Title, SRC.Title) as Title
              from  dbo.Subscriptions SUB,
	                  dbo.Sources SRC
             where  SRC.Id = SUB.SourceId");

      this.CreateView("dbo.UserArticleVW",
          @"select  ART.Url as Url,
            	      ART.Id as ArticleId,
                    ART.SourceId as SourceId,
					          ART.Title as Title,
					          ART.Description as Description,
					          ART.PublicationDate as PublicationDate,
            	      UART.IsFavorite as IsFavorite,
                    UART.UserID as UserId
            from    dbo.Articles ART,
            	      dbo.UserArticles UART
            where	ART.Id = UART.ArticleId");
    }

    public override void Down()
    {
      ApplicationDbContext context = new ApplicationDbContext();
      context.Database.ExecuteSqlCommand("DROP VIEW dbo.UserSubscriptionVW");
      context.Database.ExecuteSqlCommand("DROP VIEW dbo.UserArticleVW");

      this.CreateView("dbo.UserSubscriptionVW",
    @"select  SRC.Id as SourceId,
	                  SRC.Url as Url,
	                  SUB.UserId as UserId,
	                  SUB.IsSubscribe as IsSubscribe
            from    dbo.Subscriptions SUB,
	                  dbo.Sources SRC
            where   SRC.Id = SUB.SourceId");

      this.CreateView("dbo.UserArticleVW",
          @"select  ART.Url as Url,
            	      ART.Id as ArticleId,
                    ART.SourceId as SourceId,
            	      UART.IsFavorite as IsFavorite,
                    UART.UserID as UserId
            from    dbo.Articles ART,
            	      dbo.UserArticles UART
            where	ART.Id = UART.ArticleId");

    }
  }
}
